package ext.acae2.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @desription EXCEL2007
 * @author cnie
 * @date 2013-8-6 上午11:12:06
 */
public class ReportExcelGenerateUtil2007 {
	
	private XSSFWorkbook wb =null;
	
	private File excelFile = null;
	
	private XSSFSheet currentSheet =null;
	
	public ReportExcelGenerateUtil2007(){}
	
	public ReportExcelGenerateUtil2007(String filePathName) throws IOException, InvalidFormatException{
		OPCPackage op = null;
		excelFile = new File(filePathName);
		if (excelFile.exists()) {
			op = OPCPackage.open(filePathName);
		}
		if (op != null) {
			wb = new XSSFWorkbook(op);
			currentSheet = wb.getSheetAt(0);
		}
		op.close();
	}
	public ReportExcelGenerateUtil2007(FileInputStream inputStream) throws InvalidFormatException, IOException {
		if (inputStream != null) {
			OPCPackage op = OPCPackage.open(inputStream);
			wb = new XSSFWorkbook(op);
			currentSheet = wb.getSheetAt(0);
			op.close();
		}
		
	}
	
	public XSSFSheet getSheet(){
		return currentSheet;
	}
	
	public boolean exists() {
		return wb != null;
	}
	
	public String getParent() {
		return excelFile.getParent();
	}
	
	public String getFileName() {
		return excelFile.getName();
	}
	
	public boolean createNewFile() throws IOException {
		wb = new XSSFWorkbook();
		
		if (excelFile != null) {
			boolean dirResult = excelFile.getParentFile().mkdirs();
			
			if (dirResult || excelFile.getParentFile().exists()) {
				FileOutputStream fileOut = new FileOutputStream(getParent() + File.separator + getFileName());
			    wb.write(fileOut);
			    fileOut.close();
			} else {
				return false;
			}
		}
		return true;
	}
	
	public boolean createNewSheet(String sheetName) throws IOException {
		if (wb == null) return false;
		XSSFSheet sheet = wb.createSheet(sheetName);
		this.currentSheet = sheet;
		
		if (sheet != null) {
			return true;
		} else {
			return false;
		}
	}
	
	public void switchCurrentSheet(String sheetName) {
		if (wb == null) return ;
		XSSFSheet sheet = wb.getSheet(sheetName);
		
		if (sheet != null) {
			this.currentSheet = sheet;
		}
	}

	
	public void switchCurrentSheet(int sheetId) {
		if (wb == null) return ;
		XSSFSheet sheet = wb.getSheetAt(sheetId);
		
		if (sheet != null) {
			this.currentSheet = sheet;
		}
	}
	
	
	public int getSheetRowCount() {
		return this.currentSheet.getPhysicalNumberOfRows();
	}
	
	public boolean isExistSheet(String sheetName) {
		if (wb == null) return false;
		XSSFSheet sheet = wb.getSheet(sheetName);
		
		if (sheet == null) {
			return false;
		}
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public boolean setStringValue(int row, int col, String value) {
		if (wb == null) return false;
		XSSFRow hssfRow = currentSheet.getRow(row);
		if(hssfRow == null){
			hssfRow = currentSheet.createRow(row);
		}	
		XSSFCell cell = hssfRow.createCell((short)col);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(XSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public boolean setNumericValue(int row, int col, double value) {
		if (wb == null) return false;
		
		XSSFRow xssfRow = currentSheet.createRow(row);
		XSSFCell cell = xssfRow.createCell((short)col);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public boolean setDateValue(int row, int col, Date value, String fomat) {
		if (wb == null) return false;
		
		XSSFRow xssfRow = currentSheet.createRow(row);
		XSSFCell cell = xssfRow.createCell((short)col);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(XSSFCell.CELL_TYPE_STRING);
		
		SimpleDateFormat dateFormat = new java.text.SimpleDateFormat(fomat);
		cell.setCellValue(dateFormat.format(value));
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public boolean setBooleanValue(int row, int col, boolean value) {
		if (wb == null) return false;
		
		XSSFRow hssfRow = currentSheet.createRow(row);
		XSSFCell cell = hssfRow.createCell((short)col);
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellType(XSSFCell.CELL_TYPE_BOOLEAN);
		cell.setCellValue(value);
		
		return true;
	}
	
	public boolean mergeCells(int rowFrom, int colFrom, int rowTo, int colTo) {
		if (wb == null) return false;
		
		currentSheet.addMergedRegion(new CellRangeAddress(rowFrom,(short)colFrom,rowTo,(short)colTo));
		
		return true;
	}
	
	@SuppressWarnings("deprecation")
	public String getValue(int row, int col) {
		if (wb == null) return "";
		String value = "";
		
		XSSFRow xssfrow = currentSheet.getRow(row);
		if (xssfrow == null) {
			return "";
		}
		XSSFCell cell = xssfrow.getCell((short)col);
		if(cell == null){
			return "";
		}
		int type = cell.getCellType();
		
		if(type == XSSFCell.CELL_TYPE_STRING){
			value = cell.getStringCellValue();
		}else if (HSSFDateUtil.isCellDateFormatted(cell)) { 
	        double d = cell.getNumericCellValue();   
	        if (d == 0.0) {
	        	value = "";
	        } else {
		        Date date = HSSFDateUtil.getJavaDate(d);    
		        SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy/MM/dd");
		        value = dateFormat.format(date);
	        }
		}else if(type == XSSFCell.CELL_TYPE_NUMERIC){
			int dvalue = (int)cell.getNumericCellValue();
			value = String.valueOf(dvalue);
		}else if(type == XSSFCell.CELL_TYPE_BOOLEAN){
			value = cell.getBooleanCellValue() + "";
		}else if(type == XSSFCell.CELL_TYPE_BLANK){
			value = "";
		}else{
			value = cell.getStringCellValue();
		}

		if (value == null){
			return "";
		}
		
		return value.trim();
	}
	
	public boolean saveChanges(String fileNamePath) throws IOException {
		if (wb == null || excelFile == null) return false;
		
		//FileOutputStream fileOut = new FileOutputStream(getParent() + File.separator + getFileName());
		FileOutputStream fileOut = new FileOutputStream(fileNamePath);
	    wb.write(fileOut);
	    fileOut.close();
		
		return true;
	}
	
	public void downloadExcel(HttpServletResponse response) throws IOException {
		OutputStream os = response.getOutputStream();
		wb.write(os);
		os.flush();
		os.close();
		
		response.setStatus(HttpServletResponse.SC_OK);
		response.flushBuffer();
	}

	public XSSFWorkbook getWb() {
		return wb;
	}

	public void setWb(XSSFWorkbook wb) {
		this.wb = wb;
	}
	
	/**
	 * 锟斤拷锟斤拷锟叫憋拷锟斤拷色
	 * @param row:int row
	 * @param col:int col
	 * @param value:String 
	 * @param color:short 锟斤拷色
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public boolean setStringValueNoFram(int row, int col, String value, short color) {
		if (wb == null) return false;
		XSSFRow xssfRow = currentSheet.getRow(row);
		if(xssfRow == null){
			xssfRow = currentSheet.createRow(row);
		}	
		XSSFCell cell = xssfRow.createCell((short)col);
		XSSFCellStyle style = wb.createCellStyle();
		style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		style.setFillForegroundColor(color);
		cell.setCellStyle(style);
		cell.setCellType(XSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
		
		return true;
	}
	
	/**
	 * 锟斤拷锟斤拷锟叫边匡拷
	 * @param row:int row
	 * @param col:int col
	 * @param value:String
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public boolean setStringValueBorder(int row, int col, String value) {
		if (wb == null) return false;
		XSSFRow xssfRow = currentSheet.getRow(row);
		if(xssfRow == null){
			xssfRow = currentSheet.createRow(row);
		}	
		XSSFCell cell = xssfRow.createCell((short)col);
		XSSFCellStyle style = wb.createCellStyle();
		style.setWrapText(true);  
		style.setBorderBottom(XSSFCellStyle.BORDER_THIN);// 锟铰边匡拷
		style.setBorderTop(XSSFCellStyle.BORDER_THIN);// 锟较边匡拷
		style.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 锟斤拷呖锟�
		style.setBorderRight(XSSFCellStyle.BORDER_THIN);// 锟揭边匡拷
		cell.setCellStyle(style);
		cell.setCellType(XSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(new XSSFRichTextString(value));
		return true;
	}
	/**
	 * 锟斤拷锟斤拷锟叫边匡拷
	 * @param row:int row
	 * @param col:int col
	 * @param value:String
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public boolean setStringValueBorder1(int row, int col, String value) {
		if (wb == null) return false;
		XSSFRow xssfRow = currentSheet.getRow(row);
		if(xssfRow == null){
			System.out.println("row:"+row+", is null");
			xssfRow = currentSheet.createRow(row);
		}
		XSSFCell cell = xssfRow.createCell((short)col);
		XSSFCellStyle style = wb.createCellStyle();
		//style.setWrapText(true);  
		style.setBorderBottom(XSSFCellStyle.BORDER_THIN);// 锟铰边匡拷
		style.setBorderTop(XSSFCellStyle.BORDER_THIN);// 锟较边匡拷
		style.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 锟斤拷呖锟�
		style.setBorderRight(XSSFCellStyle.BORDER_THIN);// 锟揭边匡拷
		cell.setCellStyle(style);
		cell.setCellType(XSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(new XSSFRichTextString(value));
		currentSheet.autoSizeColumn(col);
		return true;
	}

}
